package com.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.entity.Apply;
import com.entity.User;
import com.service.CalculateSalary;
import com.util.DBUtil;

public class ManagerDao {

	public static int count = 0;
	//查询部门内全部职工的 基本信息 或薪资信息
	@SuppressWarnings("resource")
	public static ArrayList<User> getUserInfo(String userid,String condition,String conditionValue,int page,int limit) {
		PreparedStatement pst = null;
		ResultSet rs = null;
		ArrayList<User> userlist = new ArrayList<User>();
		User user = null;
		String sql = null;
		if(condition == null) {
			condition = "";
		}
		try {
			switch(condition) {
			case "rolename":
				sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and user.deptid=(select deptid from user where userid=?) and rolename like ? limit ?,?;";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, userid);
				pst.setString(2, '%'+conditionValue+'%');
				pst.setInt(3, page);
				pst.setInt(4, limit);
				break;
			default :
				sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and user.deptid=(select deptid from user where userid=?) limit ?,?";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, userid);
				pst.setInt(2, page);
				pst.setInt(3, limit);
				break;
			}
			System.out.println("sql="+pst);
			rs = pst.executeQuery();
			while(rs.next()) {
				user = new User();
				user.setUserid(rs.getString("Userid"));
				user.setDeptid(rs.getString("Deptid"));
				user.setDeptname(rs.getString("Deptname"));
				user.setRoleid(rs.getString("Roleid"));
				user.setRolename(rs.getString("Rolename"));
				user.setStatus(rs.getString("Status"));
				user.setName(rs.getString("Name"));
				user.setSex(rs.getString("Sex"));
				user.setAge(rs.getInt("Age"));
				user.setPhone(rs.getString("Phone"));
				user.setCreatetime(rs.getString("Create_time"));
				user.setBasewages(rs.getFloat("Basewages"));
				user.setMeritwages(rs.getFloat("Meritwages"));
				user.setPunishingwages(rs.getFloat("Punishingwages"));
				user.setTotalwages(rs.getFloat("Totalwages"));
				user.setOvertime(rs.getInt("Overtime"));
				user.setWeekovertime(rs.getInt("Weekovertime"));
				user.setFestovertime(rs.getInt("Festovertime"));
				user.setOvertimestandard(rs.getFloat("Overtimestandard"));
				user.setCommission(rs.getFloat("Commission"));
				user.setFestivalwages(rs.getFloat("Festivalwages"));
				user.setYearendwages(rs.getFloat("Yearendwages"));
				user.setLeavecount(rs.getInt("Leavecount"));
				user.setAbsentcount(rs.getInt("Absentcount"));
				user.setLeavestandard(rs.getFloat("Leavestandard"));
				user.setAbsentstandard(rs.getFloat("Absentstandard"));
				user.setIspay(rs.getString("Ispay"));
				user = CalculateSalary.calculate(user);
				userlist.add(user);
			//	剩余权限数据...
			}
			count = userlist.size();
		} catch(SQLException e) {
			System.out.println("经理getUserInfo查询失败");
			e.printStackTrace();
		}
		//获取数据数
		try {
			switch(condition) {
			case "rolename":
				sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and user.deptid=(select deptid from user where userid=?) and rolename like ?;";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, userid);
				pst.setString(2, '%'+conditionValue+'%');
				break;
			default :
				sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and user.deptid=(select deptid from user where userid=?)";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, userid);
				break;
			}
			rs = pst.executeQuery();
			System.out.println("Manager pst="+pst);
			rs.last();
			count = rs.getRow();
			System.out.println("count:"+count);
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return userlist;
	}
	//请假旷工提成录入
		public static boolean createApply(String userid,String operater, Object data1,Object data2,String applydescribe,String id) {
			boolean flag = false;
			PreparedStatement pst = null;
			String sql = null;
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
			String nowtime = df.format(new Date());// new Date()为获取当前系统时间
			
			try {
				switch(id) {
				case "1"://录入请假旷工
					sql = "insert into apply(applicant,applytime,`Status`,power,operater,leavecount,absentcount,applydescribe,applytype)values(?,?,'0','2',?,?,?,?,'inputPunishing')";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setString(1, userid);
					pst.setString(2, nowtime);
					pst.setString(3, operater);
					pst.setObject(4, data1);
					pst.setObject(5, data2);
					pst.setString(6, applydescribe);
					break;
				case "2"://录入奖金
					sql = "insert into apply(applicant,applytime,`Status`,power,operater,festivalbonus,yearbonus,applydescribe,applytype)values(?,?,'0','2',?,?,?,?,'inputBonus')";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setString(1, userid);
					pst.setString(2, nowtime);
					pst.setString(3, operater);
					pst.setObject(4, data1);
					pst.setObject(5, data2);
					pst.setString(6, applydescribe);
					break;
				case "3"://录入销售提成
					sql = "insert into apply(applicant,applytime,`Status`,power,operater,commission,applydescribe,applytype)values(?,?,'0','2',?,?,?,'inputCommission')";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setString(1, userid);
					pst.setString(2, nowtime);
					pst.setString(3, operater);
					pst.setObject(4, data1);
					pst.setString(5, applydescribe);
					break;
				}
				System.out.println("Manager pst="+pst);
				flag = (pst.executeUpdate()!=0);
			} catch(SQLException e) {
				e.printStackTrace();
				System.out.println("经理createApply失败");
			}
			return flag;
		}
		//获取申请列表
		@SuppressWarnings({ "null", "resource" })
		public static ArrayList<Apply> getDeptApply(String userid,String status,int page,int limit){
			ArrayList<Apply> applylist = new ArrayList<Apply>();
			PreparedStatement pst = null;
			ResultSet rs = null;
			Apply apply = null;
			String sql = null;
			//获取数据
			String iscondition = "yes";
			if(status == null || status.length() == 0) {
				iscondition = "no";
			}
			System.out.println("iscondition:"+iscondition);
			try {
				switch(iscondition) {
				case "yes":
					sql = "select * from user,apply where user.userid=apply.applicant and deptid=(select deptid from user where userid=?) and apply.status=? and power=1 limit ?,?";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setString(1, userid);
					pst.setString(2, status);
					pst.setInt(3, page);
					pst.setInt(4, limit);
					break;
				default :
					sql = "select * from user,apply where user.userid=apply.applicant and deptid=(select deptid from user where userid=?) and power=1 limit ?,?";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setString(1, userid);
					pst.setInt(2, page);
					pst.setInt(3, limit);
					break;
				}
				rs = pst.executeQuery();
				System.out.println("Manager pst="+pst);
				while(rs.next()) {
					apply = new Apply();
					apply.setApplyid(rs.getInt("Applyid"));
					apply.setApplicant(rs.getString("Applicant"));
					apply.setApplytime(rs.getString("Applytime"));
					apply.setStatus(rs.getString("Apply.Status"));
					apply.setPower(rs.getString("Power"));
					apply.setOperater(rs.getString("Operater"));
					apply.setHandle(rs.getString("Handle"));
					apply.setHandletime(rs.getString("Handletime"));
					apply.setOvertime(rs.getInt("Apply.Overtime"));
					apply.setWeekovertime(rs.getInt("Apply.Weekovertime"));
					apply.setFestovertime(rs.getInt("Apply.Festovertime"));
					apply.setLeavecount(rs.getInt("Apply.Leavecount"));
					apply.setAbsentcount(rs.getInt("Apply.Absentcount"));
					apply.setFestivalbonus(rs.getFloat("Apply.Festivalbonus"));
					apply.setYearbonus(rs.getFloat("Apply.Yearbonus"));
					apply.setCommission(rs.getFloat("Apply.Commission"));
					apply.setApplydescribe(rs.getString("Apply.Applydescribe"));
					apply.setApplytype(rs.getString("Apply.Applytype"));
					applylist.add(apply);
				}
//				System.out.println(applylist.get(0).getApplyid() + "***" + applylist.get(0).getStatus());
			}catch(SQLException e) {
				e.printStackTrace();
			}
			//获取数据数
			try {
				switch(iscondition) {
				case "yes":
					sql = "select * from user,apply where user.userid=apply.applicant and deptid=(select deptid from user where userid=?) and apply.status=? and power=1";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setString(1, userid);
					pst.setString(2, status);
					break;
				default :
					sql = "select * from user,apply where user.userid=apply.applicant and deptid=(select deptid from user where userid=?) and power=1";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setString(1, userid);
					break;
				}
				rs = pst.executeQuery();
				System.out.println("Manager pst="+pst);
				rs.last();
				count = rs.getRow();
				System.out.println("count:"+count);
			}catch(SQLException e) {
				e.printStackTrace();
			}
			return applylist;
		}
		//经理级处理申请状态（即只会处理加班录入）
		public static boolean doDeptApply(String status,String handle,String operater,int applyid,int overtime,int weekovertime,int festovertime) {
			boolean fapply = false;
			boolean fuser = false;
			PreparedStatement pst = null;
			String sql = null;
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
			String nowtime = df.format(new Date());// new Date()为获取当前系统时间
			System.out.println("DAO参数："+status+"  "+applyid+"  "+operater+"  "+overtime+"  "+weekovertime+"  "+festovertime);
			try {
				//修改申请表
				sql = "UPDATE apply set status=?,handle=?,handletime=? where applyid=?";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, status);
				pst.setString(2, handle);
				pst.setString(3, nowtime);
				pst.setInt(4, applyid);
				System.out.println("Manager 修改审核状态："+pst);
				fapply = (pst.executeUpdate()!=0);
				//当同意申请时，即status=1否则=2，修改user表，申请数据实际生效
				if("1".equals(status)) {
					sql = "UPDATE user set overtime=overtime+?,weekovertime=weekovertime+?,festovertime=festovertime+? where userid=?";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setInt(1, overtime);
					pst.setInt(2, weekovertime);
					pst.setInt(3, festovertime);
					pst.setString(4, operater);
					System.out.println("Manager 审核通过写进用户表里："+pst);
					fuser = (pst.executeUpdate()!=0);
				}else {
					fuser = true;
				}
			} catch(SQLException e) {
				e.printStackTrace();
				System.out.println("处理申请出现两个或一个失败");
			}
			if(fapply&&fuser) {
				return true;
			}else {
				return false;
			}
		}
}
